excel中使用VBA将单元格的内容转为多行

'功能实现：将sheet1中A1单元格的值拆分后 显示到sheet2的A列中
Private Sub Worksheet_Change(ByVal Target As Range)
 
 '只有A1单元格的值改变才会执行
 If Target.Address = "$A$1" Then

    '定义变量
    Dim x As Integer
    Dim sr As String

    '获取A1单元格的值,并拆分成数组
    sr = Cells(1, 1).Value
    Arry = Split(sr, ";")


    x = 0

    '先清空sheet2对应数据，从A1开始往下清空
    Do While x < 100
        x = x + 1
        Sheet2.Cells(x, 1).Value = Null
    Loop

    x = 0
    
    '再为sheet2重新赋值，从A1开始往下赋值
    For n = LBound(Arry) To UBound(Arry)
      x = x + 1
      Sheet2.Cells(x, 1).Value = Arry(n)
    Next n

End If
End Sub